package cn.com.acca.ma.dao.impl;

import cn.com.acca.ma.hibernate.impl.FindDataForSpiderWebPlotWorkImpl;
import cn.com.acca.ma.model.Board;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import java.util.Map;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.Datum;
import oracle.sql.STRUCT;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;

import cn.com.acca.ma.common.util.DateUtil;
import cn.com.acca.ma.dao.BoardIndexDao;
import cn.com.acca.ma.hibernate.util.HibernateUtil;
import cn.com.acca.ma.model.BoardIndex;
import org.jfree.data.time.Day;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;

public class BoardIndexDaoImpl extends BaseDaoImpl<BoardIndexDaoImpl> implements BoardIndexDao {

	public BoardIndexDaoImpl() {
		super();
	}

	/**
	 * 计算BOARD_INDEX表中的全部数据
	 */
	public void calculateBoardIndex() {
		logger.info("开始计算BOARD_INDEX表中的全部数据");
		
		session= HibernateUtil.currentSession();
		session.beginTransaction();
		SQLQuery query = session.createSQLQuery("{call PKG_BOARD_INDEX.CALCULATE_BOARD_INDEX()}");
		query.executeUpdate();
		session.getTransaction().commit();
		session.close();
		
		logger.info("计算BOARD_INDEX表中的全部数据完成");
	}
	
	/**
	 * 计算BOARD_INDEX表中的FIVE_DAY_RATE字段和TEN_DAY_RATE字段
	 */
	public void calculateAllBoardIndexFiveAndTenDayRate(){
		logger.info("开始计算BOARD_INDEX表中的FIVE_DAY_RATE字段和TEN_DAY_RATE字段");
		
		session= HibernateUtil.currentSession();
		session.beginTransaction();
		SQLQuery query = session.createSQLQuery("{call PKG_BOARD_INDEX.CAL_FIVE_DAY_RATE}");
		query.executeUpdate();
		query = session.createSQLQuery("{call PKG_BOARD_INDEX.CAL_TEN_DAY_RATE}");
		query.executeUpdate();
		session.getTransaction().commit();
		session.close();
		
		logger.info("计算BOARD_INDEX表中的FIVE_DAY_RATE字段和TEN_DAY_RATE字段完成");
	}

	/**
	 * 计算BOARD_INDEX表中某一日的FIVE_DAY_RATE字段和TEN_DAY_RATE字段
	 * @param multithreading
	 * @param boardDate
	 */
	public void writeBoardIndexFiveAndTenDayRateByDate(boolean multithreading, String boardDate){
		logger.info("calculate all board's five_day_rate field and ten_day_rate field by date begin");

		Session newSession = null;
		SQLQuery queryFiveDayRate;
		SQLQuery queryTenDayRate;
		if (multithreading){
			newSession = HibernateUtil.newSession();
			newSession.beginTransaction();
			queryFiveDayRate = newSession.createSQLQuery("{call PKG_BOARD_INDEX.CAL_FIVE_DAY_RATE_BY_DATE(?)}");
		} else {
			session= HibernateUtil.currentSession();
			session.beginTransaction();
			queryFiveDayRate = session.createSQLQuery("{call PKG_BOARD_INDEX.CAL_FIVE_DAY_RATE_BY_DATE(?)}");
		}

		queryFiveDayRate .setString(0, boardDate);
		queryFiveDayRate .executeUpdate();

		if (multithreading){
			queryTenDayRate = newSession.createSQLQuery("{call PKG_BOARD_INDEX.CAL_TEN_DAY_RATE_BY_DATE(?)}");
		} else {
			queryTenDayRate = session.createSQLQuery("{call PKG_BOARD_INDEX.CAL_TEN_DAY_RATE_BY_DATE(?)}");
		}

		queryTenDayRate.setString(0, boardDate);
		queryTenDayRate.executeUpdate();

		if (multithreading){
			newSession.getTransaction().commit();
			HibernateUtil.closeNewSessionFactory(newSession);
		} else {
			session.getTransaction().commit();
			session.close();
		}

		logger.info("calculate all board's five_day_rate field and ten_day_rate field by date finish");
	}

	/**
     * 计算BOARD_INDEX表的UP_DOWN_PERCENTAGE字段
     */
    public void calculateBoardIndexUpDownPercentage(){
    	logger.info("开始计算BOARD_INDEX表的UP_DOWN_PERCENTAGE字段");
    	
    	session= HibernateUtil.currentSession();
		session.beginTransaction();
		SQLQuery query = session.createSQLQuery("{call PKG_BOARD_INDEX.CAL_UP_DOWN_PERCENTAGE}");
		query.executeUpdate();
		session.getTransaction().commit();
		session.close();
    	
    	logger.info("计算BOARD_INDEX表的UP_DOWN_PERCENTAGE字段完成");
    }

	/**
	 * 计算BOARD_INDEX表中某一日的UP_DOWN_PERCENTAGE字段
	 * @param multithreading
	 * @param boardDate
	 */
	public void writeBoardIndexUpDownPercentageByDate(boolean multithreading, String boardDate){
    	logger.info("calculate all board's UP_DOWN_PERCENT field by date begin");

    	Session newSession = null;
		SQLQuery queryRate;
    	if (multithreading) {
			newSession = HibernateUtil.newSession();
			newSession.beginTransaction();
			queryRate = newSession.createSQLQuery("{call PKG_BOARD_INDEX.CAL_UP_DOWN_PERCENTAGE_BY_DATE(?)}");
		} else {
			session = HibernateUtil.currentSession();
			session.beginTransaction();
			queryRate = session.createSQLQuery("{call PKG_BOARD_INDEX.CAL_UP_DOWN_PERCENTAGE_BY_DATE(?)}");
		}

		queryRate.setString(0, boardDate);
		queryRate.executeUpdate();

		if (multithreading) {
			newSession.getTransaction().commit();
			HibernateUtil.closeNewSessionFactory(newSession);
		} else {
			session.getTransaction().commit();
			session.close();
		}

		logger.info("calculate all board's UP_DOWN_PERCENT field by date finish");
    }
    
    /**
     * 计算BOARD_INDEX表的UP_DOWN_RANK字段
     */
    public void calculateBoardIndexUpDownRank(){
    	logger.info("method calculateBoardIndexUpDownRank begin");
    	
    	session= HibernateUtil.currentSession();
		session.beginTransaction();
		SQLQuery query = session.createSQLQuery("{call PKG_BOARD_INDEX.CAL_UP_DOWN_RANK}");
		query.executeUpdate();
		session.getTransaction().commit();
		session.close();
    	
    	logger.info("method calculateBoardIndexUpDownRank finish");
    }

	/**
	 * 计算BOARD_INDEX表中某一日的UP_DOWN_RANK字段
	 * @param multithreading
	 * @param boardDate
	 */
	public void writeBoardIndexUpDownRankByDate(boolean multithreading, String boardDate){
    	logger.info("method calculateBoardIndexUpDownRankByDate begin");

    	Session newSession = null;
		SQLQuery query;
    	if (multithreading) {
			newSession = HibernateUtil.newSession();
			newSession.beginTransaction();
			query = newSession.createSQLQuery("{call PKG_BOARD_INDEX.CAL_UP_DOWN_RANK_BY_DATE(?)}");
		} else {
			session= HibernateUtil.currentSession();
			session.beginTransaction();
			query = session.createSQLQuery("{call PKG_BOARD_INDEX.CAL_UP_DOWN_RANK_BY_DATE(?)}");
		}

		query.setString(0, boardDate);
		query.executeUpdate();

		if (multithreading) {
			newSession.getTransaction().commit();
			HibernateUtil.closeNewSessionFactory(newSession);
		} else {
			session.getTransaction().commit();
			session.close();
		}

    	logger.info("method calculateBoardIndexUpDownRankByDate finish");
    }
	
	/**
	 * 返回从开始日期beginDate到结束日期endDate之间的BoardIndex数据
	 * @param beginDate
	 * @param endDate
	 * @return oardIndex类型的List列表
	 */
	@SuppressWarnings("unchecked")
	public List<BoardIndex> getBoardIndexWithinDate(String beginDate,String endDate, String boardId) {
		logger.info("method getBoardIndexWithinDate begin");
				
		String hql = "from BoardIndex b where b.date>=to_date(?,'yyyy-mm-dd') and b.date<=to_date(?,'yyyy-mm-dd') and b.boardId="+boardId+" and b.stockNumber<>0";
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		Query query = session.createQuery(hql);
		query.setString(0, beginDate);
		query.setString(1, endDate);
		List<BoardIndex> list = query.list();
		session.getTransaction().commit();
		session.close();
		
		logger.info("method getBoardIndexWithinDate finish");
		return list;
	}

	/**
	 * 根据日期，计算board_index表中某一日的记录
	 * @param multithreading
	 * @param boardDate
	 */
	public void writeBoardIndexByDate(boolean multithreading, String boardDate){
		logger.info("根据日期【" + boardDate + "】，开始计算board_index表中某一日的记录");

		Session newSession = null;
		SQLQuery query;
		if (multithreading) {
			newSession = HibernateUtil.newSession();
			newSession.beginTransaction();
			query = newSession.createSQLQuery("{call PKG_BOARD_INDEX.CALCULATE_BOARD_INDEX_BY_DATE(?)}");
		} else {
			session= HibernateUtil.currentSession();
			session.beginTransaction();
			query = session.createSQLQuery("{call PKG_BOARD_INDEX.CALCULATE_BOARD_INDEX_BY_DATE(?)}");
		}

		query.setString(0, boardDate);
		query.executeUpdate();

		if (multithreading) {
			newSession.getTransaction().commit();
			HibernateUtil.closeNewSessionFactory(newSession);
		} else {
			session.getTransaction().commit();
			session.close();
		}

		logger.info("根据日期【" + boardDate + "】，计算board_index表中某一日的记录完成");
	}
	
	/**
	 * 查询某一天涨停的股票属于哪些板块
	 */
	public void findLimitUpStockBoardByDate(String boardDate){
		logger.info("find limit up stock board by date begin");
		
		session= HibernateUtil.currentSession();
		session.beginTransaction();
		SQLQuery query = session.createSQLQuery("{call PKG_BOARD_INDEX.FIND_LIMITUP_STOCKBOARD_BYDATE(?)}");
		query.setString(0, boardDate);
		query.executeUpdate();
		session.getTransaction().commit();
		session.close();
		
		logger.info("find limit up stock board by date finish");
	}

	/**
	 * 为绘制版块蜘蛛雷达图，获取相关数据
	 * @param multithreading
	 * @param boardDate 版块日期
	 * @param dateNumber 日期数量
	 * @param limitRate 判断股票是否涨停的标准
	 * @return 返回一个二维表，表示需要的数据
	 */
    @SuppressWarnings("rawtypes")
	public List<HashMap> findDataForSpiderWebPlot(boolean multithreading, final Date boardDate, final BigDecimal dateNumber, final BigDecimal limitRate){
        logger.info("find data for spider web plot begin");
        
        final List<HashMap> list = new ArrayList<HashMap>();

        Session newSession = null;
        if (multithreading) {
			newSession = HibernateUtil.newSession();
			newSession.beginTransaction();
			newSession.doWork(new FindDataForSpiderWebPlotWorkImpl(boardDate, dateNumber, limitRate, list){});
			newSession.getTransaction().commit();
			HibernateUtil.closeNewSessionFactory(newSession);
		} else {
			session = HibernateUtil.currentSession();
			session.beginTransaction();
			session.doWork(new FindDataForSpiderWebPlotWorkImpl(boardDate, dateNumber, limitRate, list){});
			session.getTransaction().commit();
			session.close();
		}
        
        logger.info("find data for spider web plot finish");
        return list;
    }
    
    /**
     *  根据开始时间和结束时间，在board_index表中查找所有date_
     * @param beginDate
     * @param endDate
     * @return
     */
    @SuppressWarnings("unchecked")
	public List<String> getDateByCondition(String beginDate,String endDate){
    	logger.info("根据开始时间【" + beginDate + "】和结束时间【" + endDate + "】，在board_index表中查找所有date_");
		
		String hql = "select distinct to_char(date,'yyyy-mm-dd') as date from BoardIndex t where date " +
				"between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') order by date asc";
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		Query query = session.createQuery(hql);
		query.setString(0, beginDate);
		query.setString(1, endDate);
		List<String> list = query.list();
		session.getTransaction().commit();
		session.close();
		
		return list;
    }
    
    /**
     * 获取BOARD_INDEX表中，某个交易日的全部记录
     * @param date
     * @return
     */
    @SuppressWarnings("unchecked")
	public List<BoardIndex> getBoardIndexsByDate(String date){
    	logger.info("获取BOARD_INDEX表中，交易日【" + date + "】的全部记录");
    	
    	String hql="select t from BoardIndex t where t.date=to_date(?,'yyyy-mm-dd')";
    	session=HibernateUtil.currentSession();
    	session.beginTransaction();
    	Query query=session.createQuery(hql);
    	query.setParameter(0, date);
    	List<BoardIndex> list=query.list();
    	session.getTransaction().commit();
    	session.close();
    	
    	return list;
    }
    
    /**
     * 根据开始时间和结束时间，在board_index表中查找board_date
     * @param beginDate
     * @param endDate
     * @return
     */
    @SuppressWarnings("unchecked")
	public List<String> getDateByConditionForAllBoardClose(boolean multithreading, String beginDate, String endDate){
    	logger.info("method getDateByConditionForAllBoardClose begin");
		
		String hql = "select distinct to_char(t.date,'yyyy-mm-dd') as boardDate from BoardIndex t where t.date " +
				"between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') order by boardDate asc";

		Session newSession = null;
		Query query;
		if (multithreading) {
			newSession = HibernateUtil.newSession();
			newSession.beginTransaction();
			query = newSession.createQuery(hql);
		} else {
			session = HibernateUtil.currentSession();
			session.beginTransaction();
			query = session.createQuery(hql);
		}

		query.setString(0, beginDate);
		query.setString(1, endDate);
		List<String> list = query.list();

		if (multithreading) {
			newSession.getTransaction().commit();
			HibernateUtil.closeNewSessionFactory(newSession);
		} else {
			session.getTransaction().commit();
			session.close();
		}

		logger.info("method getDateByConditionForAllBoardClose finish");
		return list;
    }

	/**
	 * 根据board_id和board_date查找某个板块在某个交易日的收盘价
	 * @param multithreading
	 * @param boardNumber
	 * @param boardList
	 * @param dateList
	 * @return
	 */
	public TimeSeries[] getBoardCloseByDateAndId(boolean multithreading, Integer boardNumber, List<Board> boardList, List<String> dateList){
    	logger.info("method getBoardCloseByDateAndId begin");

		String hql = "select t.closePrice from BoardIndex t where t.date=to_date(?,'yyyy-mm-dd') and t.boardId=?";

		Session newSession = null;
		Query query;
		if (multithreading) {
			newSession = HibernateUtil.newSession();
			newSession.beginTransaction();
			query = newSession.createQuery(hql);
		} else {
			session = HibernateUtil.currentSession();
			session.beginTransaction();
			query = session.createQuery(hql);
		}

		TimeSeries[] boardSeries = new TimeSeries[boardNumber];
		for(int i=0; i<boardSeries.length; i++){
			boardSeries[i] = new TimeSeries(boardList.get(i).getName(), org.jfree.data.time.Day.class);
		}

		for(int j=0; j<boardList.size(); j++){
			for (int i = 0; i < dateList.size(); i++) {
				String[] str = dateList.get(i).toString().split("-");
				int year = Integer.parseInt(str[0]);
				int month = Integer.parseInt(str[1]);
				int day = Integer.parseInt(str[2]);
				query.setString(0, dateList.get(i).replace("-", ""));
				query.setBigDecimal(1, boardList.get(j).getId());
				BigDecimal boardClose = null;
				try {
					boardClose = (BigDecimal)query.uniqueResult();
				} catch (Exception e){
					e.printStackTrace();
				}

				boardSeries[j].add(new Day(day, month, year), boardClose);
			}
		}

		if (multithreading) {
			newSession.getTransaction().commit();
			HibernateUtil.closeNewSessionFactory(newSession);
		} else {
			session.getTransaction().commit();
			session.close();
		}

		logger.info("method getBoardCloseByDateAndId finish");
		return boardSeries;
    }
    
    /**
     * 获取从开始日期beginDate至结束日期endDate，每个交易日所有板块FIVE_DAY_RATE字段和TEN_DAY_RATE字段的平均值
     * @param beginDate
     * @param endDate
     * @return
     */
    @SuppressWarnings("rawtypes")
	public List getFiveAndTenDayRateWithinDate(boolean multithreading, String beginDate,String endDate){
    	logger.info("method getFiveAndTenDayRateWithinDate begin");
		
		String hql = "select t.date,avg(t.fiveDayRate),avg(t.tenDayRate) from BoardIndex t where t.date " +
				"between ? and ? and (t.fiveDayRate is not null and t.tenDayRate is not null) group by t.date " +
				"order by t.date asc";

		Session newSession = null;
		Query query;
		if (multithreading) {
			newSession = HibernateUtil.newSession();
			newSession.beginTransaction();
			query = newSession.createQuery(hql);
		} else {
			session = HibernateUtil.currentSession();
			session.beginTransaction();
			query = session.createQuery(hql);
		}

		query.setDate(0, DateUtil.stringToDate(beginDate));
		query.setDate(1, DateUtil.stringToDate(endDate));
		List list = query.list();

		if (multithreading) {
			newSession.getTransaction().commit();
			HibernateUtil.closeNewSessionFactory(newSession);
		} else {
			session.getTransaction().commit();
			session.close();
		}

		logger.info("method getFiveAndTenDayRateWithinDate finish");
		return list;
    }
    
    /**
     * 根据版块ID，选在开始时间（beginDate）至结束时间（endDate）的FIVE_DAY_RATE字段
     * @param beginDate
     * @param endDate
     * @param boardId
     * @return
     */
    @SuppressWarnings({ "rawtypes" })
	public List getFiveDayRate(String beginDate,String endDate,String boardId){
    	logger.info("method getFiveDayRate begin");
		
		String hql = "select t.fiveDayRate from BoardIndex t where t.boardId=? and t.boardDate between ? " +
				"and ? order by t.boardDate asc";
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		Query query = session.createQuery(hql);
		query.setString(0, boardId);
		query.setDate(1, DateUtil.stringToDate(beginDate));
		query.setDate(2, DateUtil.stringToDate(endDate));
		List fiveDayRateList = query.list();
		session.getTransaction().commit();
		session.close();
		
		logger.info("method getFiveDayRate finish");
		return fiveDayRateList;
    }
    
    /**
     * 根据版块ID，选在开始时间（beginDate）至结束时间（endDate）的TEN_DAY_RATE字段
     * @param beginDate
     * @param endDate
     * @param boardId
     * @return
     */
    @SuppressWarnings("rawtypes")
	public List getTenDayRate(String beginDate,String endDate,String boardId){
    	logger.info("method getTenDayRate begin");
		
		String hql = "select t.tenDayRate from BoardIndex t where t.boardId=? and t.boardDate between ? " +
				"and ? order by t.boardDate asc";
		session = HibernateUtil.currentSession();
		session.beginTransaction();
		Query query = session.createQuery(hql);
		query.setString(0, boardId);
		query.setDate(1, DateUtil.stringToDate(beginDate));
		query.setDate(2, DateUtil.stringToDate(endDate));
		List tenDayRateList = query.list();
		session.getTransaction().commit();
		session.close();
		
		logger.info("method getTenDayRate finish");
		return tenDayRateList;
    }
    
    /**
     * 持久化一个BoardIndex对象
     * @param boardIndex
     */
    public void save(BoardIndex boardIndex){
    	logger.info("持久化一个BoardIndex对象");
    	
    	session=HibernateUtil.currentSession();
    	session.beginTransaction();
    	session.save(boardIndex);
    	session.getTransaction().commit();
    	session.close();
    }
}
